package software.literature.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import software.literature.model.bean.TreeTBItem;
import software.literature.view.treeitem.CustomerTreeItem;

public class TreeDao extends DBDao {
	public TreeDao(String currentDB) {
		super(currentDB);

	}

	public void createDB() {
		// TODO Auto-generated method stub
		try {
			int count = 0;
			getConnection();
			// connection = DriverManager.getConnection("jdbc:sqlite:" +
			// selected);
			connection.setAutoCommit(false);
			preStm = connection
					.prepareStatement(TableDefinition.CREATE_TABLE_TREE);
			count = count + preStm.executeUpdate();
			preStm = connection
					.prepareStatement(TableDefinition.CREATE_TABLE_BBG);
			count = count + preStm.executeUpdate();
			preStm = connection
					.prepareStatement(TableDefinition.CREATE_TABLE_LITERATURE_REF_BBG);
			count = count + preStm.executeUpdate();
			preStm = connection
					.prepareStatement(TableDefinition.CREATE_TABLE_LITERATURE);
			count = count + preStm.executeUpdate();
			System.out.println("create table:" + count);
			connection.commit();
			connection.setAutoCommit(true);
			closeConnection();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {

		}
	}

	// the id and the item
	public Map<Integer, TreeTBItem> queryData() {
		System.out.println("queryData start");
		Map<Integer, TreeTBItem> itemsMap = new HashMap<Integer, TreeTBItem>();
		try {
			getConnection();
			preStm = connection.prepareStatement("select * from  tree");
			result = preStm.executeQuery();
			TreeTBItem item;
			while (result.next()) {
				item = new TreeTBItem();
				item.setId( result.getInt("id"));
				item.setData(result.getString("name"));
				item.setChildrens_id(result.getString("childrens_id"));
				item.setParentId(result.getInt("parent_id"));
				itemsMap.put(item.getId(), item);
			}
			closeConnection();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		System.out.println("queryData end");
		return itemsMap;
		// TODO Auto-generated method stub

	}

	public boolean insetTreeItem(TreeTBItem tbItem, CustomerTreeItem item) {
		// TODO Auto-generated method stub
		System.out.println("insetTreeItem start");
		try {
			getConnection();
			//iinsert into tree a new item
			preStm = connection
					.prepareStatement("insert into tree(name,parent_id) values(?,?) ");
			preStm.setString(1, tbItem.getData());
			preStm.setInt(2, tbItem.getParentId());
			int re = preStm.executeUpdate();
			if (1 == re) {
				//get the id of the new item
				preStm = connection
						.prepareStatement("select max(id) from tree ");
				result = preStm.executeQuery();
				if (result.next()) {
					int maxId = result.getInt(1);
					item.setId(maxId);
					tbItem.setId(maxId);
					//query if this item has an parent
					preStm = connection
							.prepareStatement("select childrens_id from tree  where id=?");
					preStm.setInt(1, tbItem.getParentId());
					result = preStm.executeQuery();
					if (result.next()) {
						String childrens_id = result.getString(1);
						//if has,add this item to the children list of it's parent
						preStm = connection
								.prepareStatement("update tree set childrens_id=? where id=?");
						if (null == childrens_id) {
							preStm.setString(1, "" + maxId);
						} else {
							preStm.setString(1,
									childrens_id.concat(" " + maxId));
						}
						preStm.setInt(2, tbItem.getParentId());
						re = preStm.executeUpdate();
						if (1 == re) {
							System.out.println("insetTreeItem end");
						} else {
							System.out.println("insetTreeItem end");
//							return false;
						}
					} else {
					}
				} else {
					System.out.println("insetTreeItem end");
				}
			} else {
//				return false;
			}
			closeConnection();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		closeConnection();
		System.out.println("insetTreeItem end");
		return false;

	}

	public TreeTBItem queryTreeItem(String rowid) {
		// TODO Auto-generated method stub
		return null;
	}

	public boolean deleteFromParent(int parendId, int id, String db) {
		try {
			getConnection();
			if (-1 != parendId) {
				preStm = connection
						.prepareStatement("select childrens_id from tree  where id=?");
				preStm.setInt(1, parendId);
				result = preStm.executeQuery();
				if (result.next()) {
					String childrens_id = result.getString(1);
					if (null != childrens_id) {
						String[] s = childrens_id.split(" ");
						StringBuilder build = new StringBuilder();
						for (int i = 0; i < s.length; i++) {
							if (!s[i].equals(id)) {
								build.append(s[i]);
								build.append(" ");
							}
						}
						preStm = connection
								.prepareStatement("update tree set childrens_id=? where id=?");
						preStm.setString(1, build.toString());
						preStm.setInt(2, parendId);
						int re = preStm.executeUpdate();
						closeConnection();
						if (1 == re) {
							return true;
						} else {
							return false;
						}
					}
				}
			} else {
				return true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		closeConnection();
		return false;
	}

	public boolean deleteChildren(int id,
			List<Integer> childrens_to_delete, String db) {
		try {
			getConnection();
			int le = childrens_to_delete.size();
			preStm = connection
					.prepareStatement("delete from tree where id=?");
			for (int i = 0; i < le; i++) {
				preStm.setInt(1, childrens_to_delete.get(i));
				preStm.addBatch();
			}
			connection.setAutoCommit(false);
			preStm.executeBatch();
			connection.setAutoCommit(true);
			closeConnection();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		closeConnection();
		return false;

	}

	public boolean deleteTreeItem(int parendId, int id,
			List<Integer> childrens_to_delete, String db) {

		boolean par = deleteFromParent(parendId, id, db);
		boolean ch = deleteChildren(id, childrens_to_delete, db);
		return (par && ch);
	}

	public boolean updateItemName(CustomerTreeItem item, String text) {
		// TODO Auto-generated method stub
		System.out.println("updateItemName start");
		try {
			Class.forName("org.sqlite.JDBC");
			connection = DriverManager.getConnection("jdbc:sqlite:"
					+ item.getDb());
			getConnection();
			preStm = connection.prepareStatement("update tree set name=? where id=?");
			preStm.setString(1, text);
			preStm.setInt(2, item.getId());
			int re = preStm.executeUpdate();
			closeConnection();
			if (1 == re) {
				System.out.println("updateItemName end");
				return true;
			} else {
				System.out.println("updateItemName end");
				return false;
			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			closeConnection();
		}
		System.out.println("updateItemName end");
		return false;

	}

}
